﻿using System;
using System.Collections.Generic;
using System.Data;
using Models;
using Models.Extensions;
using System.ComponentModel.Composition;

namespace MySQLProviderODBC
{
    /// <summary>
    /// Provide access to a server to get database lists and run querys
    /// </summary>
    [Export(typeof(SQPlugin.ISQPlugin))]
    public class ServerProvider : SQPlugin.ISQPlugin
    {
        /// <summary>
        /// Static property to get the server id so that other classes in this plug in can use it easily
        /// </summary>
        public static Guid ServerID
        {
            get
            {
                return new Guid("4A77C17A-CFD2-4C11-93E8-711A557447D9");
            }
        }

        #region ISPlugin

        /// <summary>
        /// Unique ID that this plug in must have so that SuperQuery can differentiate it from other plug ins
        /// </summary>
        /// <remarks>This simply links to the static ServerID property for convenience and because you cannot have
        /// static properties in interfaces</remarks>
        public Guid PlugInID
        {
            get
            {
                return ServerID;
            }
        }

        /// <summary>
        /// A discriptive name for this plug in
        /// </summary>
        public string PluginName
        {
            get
            {
                return "MySQL ODBC Driver (3.51 and 5.1)";
            }
        }

        /// <summary>
        /// Initialise and return the Edit Database user control for this plug in
        /// </summary>
        /// <returns>A user control that impliments IEditDatabase</returns>
        public SQPlugin.IEditDatabase GetEditDatabaseUI()
        {
            return new UI.EditDatabase();
        }

        /// <summary>
        /// Initialise and return the Server Details user control for this plug in
        /// </summary>
        /// <returns></returns>
        public SQPlugin.IServerDetails GetServerDetailsUI()
        {
            return new UI.ServerDetails();
        }

        /// <summary>
        /// Get a list of databases on the specified server
        /// </summary>
        /// <param name="server">The server that we want to retrieve databases from </param>
        /// <param name="errorMsg">If an error occured the message will returned here, otherwise blank</param>
        /// <returns>A list of databases or null</returns>
        public List<Database> GetDatabases(Models.Server server, out string errorMsg)
        {
            List<Database> result = null;
            errorMsg = string.Empty;

            if (server != null)
            {
                // Create a connection to the master database
                Database masterDb = new Database(server, "mysql");
                MySQL_ODBC_Driver sqlDriver = new MySQL_ODBC_Driver(masterDb);
                
                // Return a dataset containing a list of all databases on the server
                DataSet rawDbList = sqlDriver.ExecuteQuery("show databases;", out errorMsg, 30);

                // If the data set contains data them use it to create a list of databases
                if (MySQL_ODBC_Driver.CheckDataset(rawDbList))
                {
                    var databases = from db in rawDbList.Tables[0].AsEnumerable()
                                    select new Database(new Models.Server(server), db[0].ToString());

                    result = new List<Database>(databases);
                }
            }

            return result;
        }

        /// <summary>
        /// Get the results of a query based on the supplied database and query
        /// </summary>
        /// <param name="database">The database to query</param>
        /// <param name="query">The query to run</param>
        /// <returns>A query result</returns>
        public QueryResult RunQuery(Database database, Query query)
        {
            QueryResult result = null;

            if (database != null && query != null)
            {
                // Work out the type of query we are expecting to run
                Query.QueryType typeOfQuery = GetTypeOfQuery(query);

                // Create a connection to the database
                MySQL_ODBC_Driver sqlDriver = new MySQL_ODBC_Driver(database);
                string errorMsg;
                if (typeOfQuery == Query.QueryType.Table)
                {
                    // We are expecting to get a data table containing the results
                    DataSet rawData = sqlDriver.ExecuteQuery(query.QueryText, out errorMsg, query.TimeOut);
                    if (MySQL_ODBC_Driver.CheckDataset(rawData))
                    {
                        // Query succeeded so store the table and the database id
                        result = new QueryResult(rawData.Tables[0], database.DatabaseId);
                    }
                    else
                    {
                        // Query failed so store the error message and the database id
                        result = new QueryResult(null, errorMsg, database.DatabaseId);
                    }
                }
                else if (typeOfQuery == Query.QueryType.Scalar)
                {
                    // We are expecting a scalar value (just one value or text)
                    if (sqlDriver.ExecuteSqlWithGo(query.QueryText, out errorMsg, query.TimeOut))
                    {
                        // Query succeeded
                        result = new QueryResult("Query succeeded", database.DatabaseId);
                    }
                    else
                    {
                        // Query failed
                        result = new QueryResult("Query Failed", errorMsg, database.DatabaseId);
                    }
                }

            }

            return result;
        }

        /// <summary>
        /// Check to see if the supplied database model can actually connect the database it is set up to connect to
        /// </summary>
        /// <param name="database">The database to check</param>
        /// <param name="errorMsg">If an error occured the message will returned here, otherwise blank</param>
        /// <returns>True if the database connects without any errors</returns>
        public bool ValidateDatabase(Database database, out string errorMsg)
        {
            MySQL_ODBC_Driver sqlDriver = new MySQL_ODBC_Driver(database);
            return sqlDriver.CheckConnection(out errorMsg);
        }

        /// <summary>
        /// Validate the server object to see if all the correct connection details are provided
        /// </summary>
        /// <param name="server">The server object to validate</param>
        /// <returns>True if the server object contains valid connection details</returns>
        public bool ValidateServer(Models.Server server)
        {
            // Assume the validation will pass
            bool result = true;

            if (server != null)
            {
                // We must have a server name
                if (string.IsNullOrWhiteSpace(server.ServerName))
                {
                    result = false;
                }

                // We must have the folloing entries in the ServerConnection dictionary
                result = result && server.ServerConnection.ContainsKey("Version");
                result = result && server.ServerConnection.ContainsKey("Port");
                result = result && server.ServerConnection.ContainsKey("UserName");
                result = result && server.ServerConnection.ContainsKey("Password");
            }
            else
            {
                // No server object so fail the validation
                result = false;
            }

            return result;
        }

        #endregion

        /// <summary>
        /// Work out the type of query we should execute based on the supplied query object
        /// </summary>
        /// <param name="query">The query object to inspect</param>
        /// <returns>The type of query we need to execute</returns>
        private Query.QueryType GetTypeOfQuery(Query query)
        {
            Query.QueryType typeOfQuery;

            switch (query.TypeOfQuery)
            {
                case Query.QueryType.Scalar:
                case Query.QueryType.Table:
                {
                    typeOfQuery = query.TypeOfQuery;
                    break;
                }
                default:
                {
                    // User has set query type to auto so use Table type if the query text starts with SELECT,
                    // otherwise use Scalar
                    if (query.QueryText.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
                    {
                        typeOfQuery = Query.QueryType.Table;
                    }
                    else
                    {
                        typeOfQuery = Query.QueryType.Scalar;
                    }
                    break;
                }
            }

            return typeOfQuery;
        }
    }
}
